package com.cyy.dao;

import com.cyy.bean.collect;
import com.cyy.bean.collect;
import com.cyy.bean.record;
import com.cyy.util.dbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class collectDao extends recordDao{
    public ArrayList<record> selectAll(String name,int type1) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql;
        if(type1==0) {
            sql = "select productName,price,time,num,usability from collect inner join record on collect.recordId=record.recordId where record.userName=?";
        }else
        {
            sql = "select productName,price,time,num,usability from collect inner join record on collect.recordId=record.recordId where record.userName=? and usability=1";
        }
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,name);
        ResultSet resultSet = pstmt.executeQuery();
        collect data=null;
        int price,num,usability;
        String time,productName;
        ArrayList<record> datas=null;
        while(resultSet.next())
        {
            productName=resultSet.getString("productName");
            price=resultSet.getInt("price");
            time=resultSet.getString("time");
            num=resultSet.getInt("num");
            usability=resultSet.getInt("usability");
            data=new collect(0,name,productName,price,num,time,usability);
            if(datas==null)
            {
                datas=new ArrayList<>();
            }
            datas.add(data);
        }
        dbUtil.close(resultSet,pstmt,conn);
        return datas;
    }

    public void add(record c) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="insert into record(userName,productName,price,num,time,usability) values(?,?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,c.getUserName());
        pstmt.setString(2,c.getProductName());
        pstmt.setInt(3,c.getPrice());
        pstmt.setInt(4,c.getNum());
        pstmt.setString(5,c.getTime());
        pstmt.setInt(6,c.getUsability());
        int i = pstmt.executeUpdate();
        int id=getMaxid();
        String sql1="insert into collect values(?)";
        PreparedStatement pstmt1 = conn.prepareStatement(sql1);
        pstmt1.setInt(1,id);
        int i1 = pstmt1.executeUpdate();
        dbUtil.close(null,pstmt,conn);
        dbUtil.close(null,pstmt1,conn);
    }

    public int selectByProductAndUser(String userName,String productName) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="select * from collect where recordId in (select recordId from record where userName=? and productName=?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        pstmt.setString(2,productName);
        ResultSet resultSet = pstmt.executeQuery();
        if(resultSet.next())
        {
            return resultSet.getInt("recordId");
        }
        dbUtil.close(null,pstmt,conn);
        return 0;
    }
    public void deleteByUserAndProduct(String userName, String productName) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="select recordId from collect where recordId in (select recordId from record where userName=? and productName=?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        pstmt.setString(2,productName);
        ResultSet resultSet= pstmt.executeQuery();
        String sql1="",sql2="";
        PreparedStatement pstmt1=null,pstmt2=null;
        while(resultSet.next())
        {
            int recordId = resultSet.getInt("recordId");
            sql1="delete from collect where recordId=?";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setInt(1,recordId);
            pstmt1.executeUpdate();
            sql2="delete from record where recordId=?";
            pstmt2 = conn.prepareStatement(sql2);
            pstmt2.setInt(1,recordId);
            pstmt2.executeUpdate();
        }
        dbUtil.close(null,pstmt,conn);
        dbUtil.close(null,pstmt1,conn);
        dbUtil.close(null,pstmt2,conn);
    }

    public int getMaxidByname(String name) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="select * from collect where recordId in (select recordId from record where userName=?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,name);
        ResultSet resultSet = pstmt.executeQuery();
        int index=0;
        while(resultSet.next())
        {
            index+=1;
        }
        return index;
    }
}
